/************************************************************************************/
/*  Program name CAPFNDIMP_PROFILE.sql                                              */
/*  Creater:  WONG Wing Kin (PCCW - UniHub)                                         */
/*************   History   *************************                                */
/* 30 NOV, 2005   WONG Wing Kin         First created                               */
/************************************************************************************/
SET SERVEROUTPUT ON SIZE 1000000;

--ALTER TRIGGER FND_PROFILE_OPTION_VALUES_CT1 DISABLE;

DECLARE
	lv_Prog_ID		VARCHAR2(100)	:=	'CAPFNDIMP_PROFILE';

	lv_Scc_Count	NUMBER			:=	0;
	lv_Err_Count	NUMBER			:=	0;
	lv_Err_Msg		VARCHAR2(500)	:=	'No Error';

	CURSOR Cur_F IS
	SELECT
		*
	FROM
		CRST_FND_PROFILE;

	lv_Next_Seq_ID	NUMBER;
	lv_Pf_Value		VARCHAR2(240);

BEGIN

	FOR C1 IN Cur_F LOOP
		BEGIN
			IF		C1.OPT_TYPE	=	'PER_BUSINESS_GROUP_ID'		THEN
				SELECT	TO_CHAR(CAPBRIMP_MAPPING.GetBGMapping(Business_Group_ID))
				INTO	lv_Pf_Value
				FROM	PER_BUSINESS_GROUPS
				WHERE	Name						=	C1.Name;

			ELSIF	C1.OPT_TYPE	=	'PER_SECURITY_PROFILE_ID'	THEN
				SELECT	TO_CHAR(Security_Profile_ID)
				INTO	lv_Pf_Value
				FROM	PER_SECURITY_PROFILES
				WHERE	Security_Profile_Name		=	C1.Name
				AND Business_group_id = capbrimp_common.getBGID(C1.Name);

			ELSIF	C1.OPT_TYPE	=	'ORG_ID'					THEN
				SELECT	TO_CHAR(CAPBRIMP_MAPPING.GetOrgMapping(Organization_ID))
				INTO	lv_Pf_Value
				FROM	HR_ALL_ORGANIZATION_UNITS
				WHERE	Name						=	C1.Name;				

			ELSIF	C1.OPT_TYPE	=	'GL_SET_OF_BKS_NAME'		THEN
				--SELECT	TO_CHAR(Short_Name)
				SELECT	TO_CHAR(Name)
				INTO	lv_Pf_Value
				FROM	GL_SETS_OF_BOOKS
				WHERE	Short_Name					=	C1.Name;

			ELSIF	C1.OPT_TYPE	=	'GL_SET_OF_BKS_ID'		THEN
				--SELECT	TO_CHAR(Short_Name)
				SELECT	TO_CHAR(CAPBRIMP_MAPPING.GetSOBIDMapping(SET_OF_BOOKS_ID))
				INTO	lv_Pf_Value
				FROM	GL_SETS_OF_BOOKS
				WHERE	Short_Name					=	C1.Name;
				
			ELSIF	C1.OPT_TYPE	=	'BUDGETARY_CONTROL_OPTION'	THEN
				SELECT	TO_CHAR(BC_Option_ID)
				INTO	lv_Pf_Value
				FROM	GL_BC_OPTIONS
				WHERE	BC_Option_Name				=	C1.Name;

			ELSIF	C1.OPT_TYPE	=	'PO_AUTOCREATE_DATE'	THEN
				lv_Pf_Value	:=		C1.Name;

			ELSIF	C1.OPT_TYPE	=	'CURRENCY:NEGATIVE_FORMAT'	THEN
				lv_Pf_Value	:=		C1.Profile_Option_Value;

			ELSE
				lv_Pf_Value	:=		NULL;

			END IF;

			INSERT INTO FND_PROFILE_OPTION_VALUES
			VALUES
			(
			C1.APPLICATION_ID,			--Should be not Change
			C1.PROFILE_OPTION_ID,		--System Profile ID should be same
			C1.LEVEL_ID,				--fixed (10003)
			CAPBRIMP_COMMON.GetRespID(C1.RESPONSIBILITY_KEY),		--LEVEL_VALUE,
			C1.LAST_UPDATE_DATE,
			CAPBRIMP_COMMON.GetUser(C1.LAST_UPDATED_BY_NAME),		--LAST_UPDATED_BY,
			C1.CREATION_DATE,
			CAPBRIMP_COMMON.GetUser(C1.CREATED_BY_NAME),			--CREATED_BY,
			C1.LAST_UPDATE_LOGIN,
			lv_Pf_Value,											--PROFILE_OPTION_VALUE,
			C1.LEVEL_VALUE_APPLICATION_ID
			);
		EXCEPTION
			WHEN OTHERS THEN
				DBMS_OUTPUT.Put_Line(SQLERRM);
				lv_Err_Count	:=	lv_Err_Count + 1;
		END;
		lv_Scc_Count	:=	lv_Scc_Count	+	1;
	END LOOP;

	IF	lv_Err_Count	>	0	THEN
		ROLLBACK;
	END IF;

	INSERT INTO CAPCNV_LOG(
		PROG_ID,
		PROCESS_DATE,
		RECORD_PROCESSED,
		RECORD_REJECTED,
		RECORD_EXTRACTED,
		ERR_MSG
	) VALUES (
		lv_Prog_ID,
		SYSDATE,
		lv_Scc_Count,
		lv_Err_Count,
		lv_Scc_Count-lv_Err_Count,
		lv_Err_Msg
	);

	COMMIT;
END;
/

--ALTER TRIGGER FND_PROFILE_OPTION_VALUES_CT1 ENABLE;
